#!/usr/bin/python
# -*- coding: utf-8 -*-

from sqlite3 import dbapi2 as sqlite, OperationalError
from tmthinkqueries import *
import simplejson as json

def fetchQuery(query):
    #print "query:", query
    try:
        return sqlite.connect('/srv/tmradioweb/tmradio/ardj.sqlite').cursor().execute(query).fetchall()
    except Exception as e:
        print "fetchQuery error:",e
        print "wrong query:", query

SORT_LIST={
    'id':           {'title': u'id',            'selected': False},
    'artist':       {'title': u'Исполнитель',   'selected': False},
    'title':        {'title': u'Название',      'selected': False},
    'weight':       {'title': u'Рейтинг',       'selected': False},
    'count':        {'title': u'Проигрываний',  'selected': False},
}
SORT_LIST['weight']['selected'] = True

"""
ardj base structure:
    table           fields
    --------- -----------------------------------------------
    tracks    id, count, artist, title, weight, last_played
    labels    id, track_id, label
    playlog   ts, track_id, listeners
    votes     id, track_id, email, vote, ts
"""

QUERY_TEMPLATE = """
    SELECT DISTINCT fieldsString FROM tracks 
    WHERE nameWhereString
        AND labelString
        AND weight>0
    orderString orderDir
    limitString
"""

def makeQuery(query, **kwargs):
    if kwargs.get('name', None):
        n = kwargs['name']
        # nameWhereString = '(LOWER(artist) LIKE "%%'+n+'%%" OR LOWER(title) LIKE "%%'+n+'%%")'
        id_search = ''
        if n.isdigit(): id_search = 'OR id = {0}'.format(n)
        nameWhereString = u'(LOWER(artist) LIKE "%{name}%" OR LOWER(title) LIKE "%{name}%" {id_search})'.format(name = n, id_search = id_search)
        
        #nameWhereString = '(CHARINDEX("%s", LOWER(artist)) IS NOT NULL) OR (CHARINDEX("%s", LOWER(title)) IS NOT NULL)'%(n, n)
    else: nameWhereString = '1'
    query = query.replace('nameWhereString', nameWhereString)
    
    if kwargs.has_key('fields'):
        fieldsString = kwargs['fields']
    else:
        fieldsString = """id, count, artist, title, weight, tagslist"""
    query = query.replace('fieldsString', fieldsString)
    query = query.replace('tagslist', """(SELECT group_concat(label) FROM labels 
            WHERE labels.track_id = tracks.id AND NOT label LIKE "%:%")
            """)
    if kwargs.has_key('order'):
        orderString = "ORDER BY "+kwargs['order']
    else:
        orderString = "ORDER BY artist"
    query = query.replace('orderString', orderString)

    if kwargs.has_key('orderDir'):
        orderDir = kwargs['orderDir']
    else: orderDir = 'ASC'
    query = query.replace('orderDir', orderDir)

    if kwargs.has_key('limit'):
        limitString = "LIMIT "+str(kwargs['limit'])
    else:
        limitString = "LIMIT 50"
    query = query.replace('limitString', limitString)
    labelString = '1'
    if kwargs.get('labels', False):
        labelString = []
        for label in kwargs['labels']:
            if label[0] != '-':
                labelString+=["id IN (SELECT track_id FROM labels WHERE label = '%s')"%label]
            else:
                labelString+=["id NOT IN (SELECT track_id FROM labels WHERE label = '%s')"%label.strip('-')]
        labelString = " AND ".join(labelString)
    query = query.replace('labelString', labelString)
    return query

def getSpecInfo(qType):
    fields, query = False, False
    if qType == 'welcome': return "welcome!"
    if qType == 'hitList': fields, query = "id, count, artist, title, weight", QHitList
    if qType == 'popularArtists': fields, query = "artist, weight, tracks, avWeight", QPopularArtists
    if qType == 'newTracksPlayed': fields, query = "id, count, artist, title, weight", QNewTracksPlayed
    if qType == 'bestNewTracks': fields, query = "id, count, artist, title, weight", QBestNewTracks
    if qType == 'bestNewTracks2': fields, query = "id, count, artist, title, weight, avWeight", QBestNewTracks2
    if qType == 'forgottenTracks': fields, query = "id, count, artist, title, weight", QForgottenTracks
    if qType == 'bestCalmTracks': fields, query = "id, count, artist, title, weight", QBestCalmTracks
    if qType == 'bestLoungeTracks': fields, query = "id, count, artist, title, weight", QBestLoungeTracks
    if qType == 'bestCovers': fields, query = "id, count, artist, title, weight", QBestCovers
    if qType == 'mostBookmarked': fields, query = "id, count, artist, title, weight, bmcount", QMostBookmarked  
    if qType == 'neverPlayedArtists': fields, query = "artist", QNeverPlayedArtists
    if qType == 'neverPlayedTracks': fields, query = "id, count, artist, title, length, weight", QNeverPlayedTracks
    if qType == 'preshowMusic': fields, query = "id, count, artist, title, weight", QPreshowMusic
    fields = fields.replace(" ", "").split(",")
    if fields and query:
        data = fetchDict(fields, fetchQuery(query))
        result = {'fields': fields, 'data': data}
        return result
    return """available requests: popularArtists, newTracksPlayed, forgottenTracks, 
                    bestCalmTracks, bestCovers, neverPlayedArtists, neverPlayedTracks<br>
                    for example: ?spec=bestCovers
                """

def fetchDict(fields, query_r):
    if not query_r: return []
    if type(fields).__name__ != "list": fieldsList = fields.replace(' ',"").split(',')
    else: fieldsList = fields
    print "fields", fieldsList
    result = []
    for dataList in query_r:
        row = {}
        for num, key in enumerate(fieldsList): 
            value = dataList[num]
            if type(value).__name__=='float': value = str(round(value,2))
            if type(value).__name__=='int': value = str(value)
            row[key] = value.replace("'", "`").replace('"',"&quot;")
        result.append(row)
    return result
